---
title: "xxxxxxx"
output: 
  flexdashboard::flex_dashboard:
    theme:
      version: 4
      bootswatch: sketchy
    orientation: rows
    vertical_layout: fill
    source_code: embed
---

```{r setup, include=FALSE}
library(flexdashboard)
library(ggplot2)
library(naniar)
library(readxl)
library(tidyverse)
library(plotly)
library(ggmap)
library(gganimate)
library(gifski)
library(png)
# install.packages("bslib")
library(bslib)
library(kableExtra)
```


Overview {data-icon="fa-globe"}
=============================
Inputs {.sidebar data-width=400}
-------------------------------------

### Table of Contents





Row{data-height=150}
--------------------------------------

### XXXXX

![](https://media.giphy.com/media/3otO6zntMrmhpvaYX6/giphy.gif)



Row{data-height=600}
--------------------------------------

### Introduction

XXXXXXXXXXXXXXXXXXXXXx


```{r, include=FALSE}
excel_sheets("data/API_4_DS2_en_excel_v2_2252569.xls")
data_income <- read_excel("data/CLASS.xls", range = anchored("C5", dim = c(220,5))) %>% 
  select(-X) %>% 
  filter(Economy != "x")
```

```{r, include=FALSE}
df<- read_excel("Data/API_4_DS2_en_excel_v2_2252569.xls", sheet = 1, range = "A4:BM42772") %>% 
  select("Country Name", 
         "Country Code",
         "Indicator Name", 
         "2000":"2019") %>% 
  rename("country_name" = "Country Name",
         "indicator" = "Indicator Name",
         "country_code" = "Country Code")
```

```{r, include=FALSE}
df %>%
  group_by(country_name) %>%
  miss_var_summary() %>% 
  arrange(-pct_miss)
```

```{r}
df_filter <- df %>% 
  filter(!(country_name %in% c("Faroe Islands",
                          "Greenland",
                          "Isle of Man",
                          "Not classified",
                          "St. Martin (French part)",
                          "Northern Mariana Islands",
                          "Kosovo",
                          "American Samoa",
                          "Gibraltar",
                          "Sint Maarten (Dutch part)",
                          "Turks and Caicos Islands",
                          "Channel Islands",
                          "Guam",
                          "French Polynesia",
                          "Curacao",
                          "New Caledonia",
                          "Virgin Islands (U.S.)",
                          "Nauru",
                          "Palau",
                          "Somalia",
                          "Guinea-Bissau")))
       
```

```{r, include=FALSE}
data_joined <- df_filter %>% 
    left_join(data_income, by = c("country_name" = "Economy",
                                "country_code" = "Code")) 
```





Literacy Rate {data-icon="ion-android-search"}
=============================
Row{data-height=150}
----------------------------------

### **Literacy Rate for Adult and Youth**
```{r}
valueBox(value = "Literacy Rate for Adult and Youth 📚", icon = "ion-university", caption = "Global - Regional - Income Levels", color = "skyblue")
```


Row {.tabset data-height=700}
--------------------------------------

### **Global Literacy Rate**

```{r longdata-my}
# filter and pivot data to longer form
lit_long <- data_joined %>% 
  filter(str_detect(indicator, "Literacy")) %>% 
  filter(str_detect(indicator, "total")) %>% 
  pivot_longer(cols = c("2000":"2019"),
               names_to = "year",
               values_to = "rate")
# remove strings in indicator variable
lit_long$indicator <- lit_long$indicator %>% 
  substring(16) %>%
  str_remove("\\(") %>% 
  str_remove("\\)") %>% 
  str_remove("\\%") %>% 
  str_remove("total  of people ")
```

```{r plot1my, eval = FALSE, catche = TRUE}
# animation
plot1 <- lit_long %>% 
  filter(country_name == "World") %>% 
  ggplot(aes(x = year,
             y = rate,
             fill = indicator)) +
  geom_col(position = "dodge", width = 0.7) +
  theme_light() +
  ylim(0, 100) +
  theme(axis.text.x = element_text(angle = 60, vjust = 0.5, hjust=0.5)) +
  scale_fill_brewer(palette = "Paired") +
  labs(x = "Year",
       y = "Literacy Rate",
       fill = "Age") +
  ggtitle("Global Literacy Rate for Adult and Youth between 2000-2019") +
  transition_time(as.numeric(year)) +
  enter_grow() +
  shadow_mark(past = TRUE)

animate(plot1, fps = 20, duration = 15, end_pause = 80)
```



### **Regional Literacy Rate**

```{r plot2my, eval = FALSE, catche = TRUE}
# animation
plot2 <- lit_long %>% 
  filter(country_name %in% c("Central Europe and the Baltics",
                             "East Asia & Pacific",
                             "Europe & Central Asia",
                             "Latin America & Caribbean",
                             "Middle East & North Africa",
                             "South Asia",
                             "Sub-Saharan Africa")) %>% 
  group_by(indicator) %>% 
  ggplot(aes(x = year,
             y = rate,
             group = country_name,
             color = country_name)) +
  geom_line() +
  facet_wrap(~indicator) +
  theme_light() +
  theme(axis.text.x = element_text(angle = 90, vjust = 0.5, hjust=0.5)) +
  scale_colour_brewer(palette = "Paired") +
  labs(x = "Year",
       y = "Literacy Rate",
       color = "Regions") +
  ggtitle("Literacy Rate for Adult and Youth in Differenet Regions") +
  transition_reveal(as.numeric(year)) +
  enter_grow() +
  shadow_mark(past = TRUE)

animate(plot2, fps = 20, duration = 8, end_pause = 80)
```



### **Literacy Rate for Different Income Groups**

```{r plot3my, eval = FALSE, catche = TRUE}
# animation
plot3 <- lit_long %>% 
  filter(country_name %in% c("Low income",
                             "Lower middle income",
                             "Middle income",
                             "Upper middle income")) %>% 
  ggplot(aes(x = year,
             y = rate,
             fill = indicator)) +
  geom_col(position = "dodge") +
  facet_wrap(~country_name, 
             ncol = 2) +
  theme_light() +
  theme(axis.text.x = element_text(angle = 90, vjust = 0.5, hjust=0.5)) +
  scale_fill_brewer(palette = "Paired") +
  labs(x = "Year",
       y = "Literacy Rate",
       fill = "Age") +
  ggtitle("Literacy Rate for Adult and Youth in Differenet Income Groups")  +
  transition_time(as.numeric(year)) +
  enter_grow() +
  shadow_mark(past = TRUE)
  
animate(plot3, fps = 20, duration = 20, end_pause = 80)
```

Column {.sidebar data-width=400}
--------------------------------------
> Key Findings

* Global 

  * There was a slow and steady increase in literacy rate for adult aged 15 and above and youth aged 15-24 globally over the years.

* Regional

  * There was a significant increase in the literacy rate in adult aged 15 and above in Middle East & North Africa, South Asia and Sub-Saharan Africa.
  * South Asia and Sub-Saharan Africa also had improved the literacy rate tremendously in the youth age group over the years.
  * Both East Asia & Pacific and Latin America & Caribbean had an 5% increase in literacy rate in the adult group where as the literacy rate increase approximately 4% in the youth group in Latin America & Caribbean and a small increase in the youth group in East Asia & Pacific. 
  * Central Europe and the Baltics and Europe & Central Asia had maintained a high literacy rate in both age groups over the years..

* Different income levels
  
  * Both adult and youth groups that have higher income tend to have higher a level of literacy skills.

* Conclusion


Education spending {data-icon="ion-android-search"}
=============================
Row{data-height=150}
--------------------------------------


### **Government expenditure on education (% of GDP)**
```{r}
valueBox(value = "Government expenditure on education, total (% of GDP) 🏫", icon = "ion-university", caption = "Average government expenditure on education (% of GDP), 2000-2019", color = "orange")
```


Row {.tabset data-height=700}
--------------------------------------

```{r}
data_zb <- data_joined %>% 
  pivot_longer(cols = c("2000":"2019"),
               names_to = "year",
               values_to = "value") %>% 
  filter(indicator %in% c("Government expenditure on education, total (% of GDP)",
                          "Government expenditure on education, total (% of government expenditure)",
                          "Expenditure on secondary education (% of government expenditure on education)",
                          "Government expenditure per student, secondary (% of GDP per capita)",
                          "Expenditure on primary education (% of government expenditure on education)",
                          "Government expenditure per student, primary (% of GDP per capita)")) %>% 
  pivot_wider(names_from = indicator,
              values_from = value) %>% 
  drop_na(Region) %>% 
  rename("income_group" = "Income group",
         "region" = "Region")
selected_indicators = c("Government expenditure on education, total (% of GDP)",
                          "Government expenditure on education, total (% of government expenditure)",
                          "Expenditure on secondary education (% of government expenditure on education)",
                          "Government expenditure per student, secondary (% of GDP per capita)",
                          "Expenditure on primary education (% of government expenditure on education)",
                          "Government expenditure per student, primary (% of GDP per capita)")
new_name = c("ex_total_GDP",
             "ex_total_gov_ex",
             "ex_secondary",
             "ex_per_student_secondary",
             "ex_primary",
             "ex_per_student_primary")
for(i in 1:6) names(data_zb)[names(data_zb) == selected_indicators[i]] = new_name[i]
data_zb$country_name[which(data_zb$country_name == "United States")] = "USA"
data_zb$country_name[which(data_zb$country_name == "United Kingdom")] = "UK"
data_zb$country_name[which(data_zb$country_name == "Russian Federation")] = "Russia"
data_zb$country_name[which(data_zb$country_name == "Iran, Islamic Rep.")] = "Iran"
data_zb$country_name[which(data_zb$country_name == "Congo, Rep.")] = "Democratic Republic of the Congo"
data_zb$country_name[which(data_zb$country_name == "Venezuela, RB")] = "Venezuela"
data_zb$country_name[which(data_zb$country_name == "Yemen, Rep.")] = "Yemen"
data_zb$country_name[which(data_zb$country_name == "Egypt, Arab Rep.")] = "Egypt"
```
### **Government expenditure on education (% of GDP), 2000-2019**
```{r plot1-zb, fig.cap = "Government expenditure on education (% of GDP), 2000-2019"}
data1_zb <- data_zb %>% 
  group_by(country_name) %>% 
  summarise(mean = mean(ex_total_GDP, na.rm = TRUE))
world_map <- map_data("world")
data1_zb_wm <- world_map %>% 
  left_join(data1_zb, by = c("region" = "country_name"))
map_zb <- data1_zb_wm %>% 
  ggplot(aes(long, lat, group = group))+
  geom_polygon(aes(fill = mean), color = "white")+
  scale_fill_viridis_c(option = "C") +
  ggtitle("World map", subtitle = "Average government expenditure on education (% of GDP), 2000-2019")

ggplotly(map_zb)
```

### **Government expenditure on education by income group**

```{r plot2-zb, fig.cap = "Government expenditure on education (% of GDP), by income group"}
data2_zb <- data_zb %>% 
  filter(year != "2019") %>% 
  group_by(income_group, year) %>% 
  summarise(mean = mean(ex_total_GDP, na.rm = TRUE))

plot2zb <- ggplot(data2_zb, aes(x=year, y=mean, group = income_group)) + 
    geom_line(aes(color = income_group)) +
  geom_point(aes(color = income_group))

ggplotly(plot2zb)
```

### **Government expenditure on education by region**

```{r plot3-zb, fig.cap = "Government expenditure on education (% of GDP), by region"}
data3_zb <- data_zb %>% 
  filter(year != "2019",
         region != "North America") %>% 
  group_by(region, year) %>% 
  summarise(mean = mean(ex_total_GDP, na.rm = TRUE)) 
plot3zb <- data3_zb %>% 
  ggplot(aes(x=year, y=mean, group = region)) + 
    geom_line(aes(color = region)) +
  geom_point(aes(color = region))

ggplotly(plot3zb)
```

### **Government expenditure on education by income group**
```{r plot4-zb, fig.cap = "Government expenditure on education (% of GDP), by income group, in 2000 and 2015"}
data_zb %>% 
  filter(year != "2019") %>% 
  group_by(income_group, year) %>% 
  summarise(mean = mean(ex_total_GDP, na.rm = TRUE)) %>% 
  pivot_wider(names_from = year,
              values_from = mean) %>% 
  select(income_group, "2000", "2015") %>% 
  mutate(change = (`2015` - `2000`)/`2000`*100) %>% 
  kable(col.names = c("Income groups", 
                      "2000",
                      "2015",
                      "Percentage change"),
        align = "c",
        caption = "Total government expenditure on educationby income group in 2000 and 2015",
        digits = 2) %>% 
  kable_styling(bootstrap_options = c("striped", "hover"))
```

Column {.sidebar data-width=400}
--------------------------------------
> Key Findings

* From the map

  * The average government spending on education across countries ranged between 1.2% - 11.5% of their GDP.

* By income group

  * What evident on the graph is that the low income countries are devoted much lesser proportion of their GDP but also we can see that spending has increased on average for those countries. 
  * On the other hand, high income countries spending more share of their GDP roughly between 4.5% to 5%.
  * However the data had many missing values, a broad upward trend can be observed.

* By region
  
  * During the time period, South Asia and Sub-Saharan Africa had the lowest spending ranging between 3-4% but again, the plot shows upward trend for those regions. 
  * For the other regions, it has been relatively stable over time.

* From table
  * The increase in education spending is evident for the majority of countries. 
  * But it remained at same level for upper middle income countries.

* Conclusion
  * Overall, it can be concluded that the total amount of global resources spent on education is increasing over the world.
  
Children Out of School {data-icon="ion-android-search"}
=============================
Row{data-height=150}
--------------------------------------

### **Percentage of children out of primary school**
```{r}
valueBox(value = "Percentage of children out of primary school 🏫", icon = "ion-university", caption = "Regional - Gender Differnce", color = "green")
```

Row {.tabset data-height=700}
--------------------------------------

### **Percentage of Children Out of School - Total**
```{r fig.cap="Percentage of children out of school in primary school age", fig.width=12, fig.height=6}
df_outsch <- data_joined %>% 
  filter(indicator %in% c("Children out of school (% of primary school age)",
                          "Children out of school, male (% of male primary school age)",
                          "Children out of school, female (% of female primary school age)")) %>% 
  pivot_longer(cols = "2000":"2019",
               names_to = "year",
               values_to = "value") %>% 
  pivot_wider(id_cols = -indicator,
              names_from = indicator,
              values_from = value) %>% 
  rename("children_out_of_school_tot_%" = `Children out of school (% of primary school age)`,
         "children_out_of_school_m_%" = `Children out of school, male (% of male primary school age)`,
         "children_out_of_school_f_%" = `Children out of school, female (% of female primary school age)`)

outsch <- df_outsch %>% 
  filter(country_name %in% c("East Asia & Pacific",
                             "Europe & Central Asia",
                             "Latin America & Caribbean",
                             "Middle East & North Africa",
                             "North America",
                             "South Asia",
                             "Sub-Saharan Africa")) 

income_level <- data_income %>% 
  select(Region,`Income group`)

os_clean <- outsch %>% 
  left_join(income_level,
            by = c("country_name" = "Region")) %>% 
  select(-c(country_code, Region, `Income group.x`)) %>% 
  rename("region" = country_name,
         "income_group" = `Income group.y`) %>% 
  mutate(year = as.numeric(year))

os_clean <- unique(os_clean[ , 1:5])

plot1_cos <- os_clean %>% 
  ggplot(aes(x = year, y = `children_out_of_school_tot_%`, colour = region)) +
  geom_line() +
  labs(x = "Year", y = "Percentage") +
  scale_fill_brewer(palette="Paired") +
  theme_light() 

ggplotly(plot1_cos)
```   

### **Difference in Percentage by Gender**

```{r fig.cap="Percentage of children out of school in primary school age by gender", fig.width=12, fig.height=6}
os_gender <- os_clean %>% 
  rename("male" = `children_out_of_school_m_%`,
         "female" = `children_out_of_school_f_%`) %>% 
  pivot_longer(cols = c(male,female),
               names_to = "gender",
               values_to = "percentage") %>% 
  select(-`children_out_of_school_tot_%`)

plot2_cos <- os_gender %>% 
ggplot(aes(x= year, y = percentage, fill = gender)) +
  facet_wrap(~region,
             ncol = 2) +
  geom_bar(stat = "identity",
           position = "dodge") +
  xlim(c(2000,2019)) +
  labs(x = "Year", y = "Percentage") +
  scale_fill_brewer(palette="Paired") +
  theme_light() 

ggplotly(plot2_cos)

```

### **Difference in Percentage by Gender in 2000 and 2019**

```{r}
os_diff <- os_clean %>% 
  filter(year %in% c(2000,2019)) %>% 
  group_by(region, year) %>% 
  summarise(per_diff = `children_out_of_school_f_%` - `children_out_of_school_m_%`,
            .groups = "drop") %>% 
  pivot_wider(names_from = year,
              values_from = per_diff)

os_diff[order(-os_diff$`2000`),] %>% 
  kable(col.names = c("Region", 
                      "Difference in percentage - 2000",
                      "Difference in percentage - 2019"),
      align = "c",
      caption = "Difference in percentage of primary-school-age children out of school between males and females", 
      digits = 2) %>% 
  kable_styling(bootstrap_options = c("striped","hover"),
                latex_options = "HOLD_position")
```

Column {.sidebar data-width=400}
--------------------------------------
> Key Findings

* From the line graph 

  * Sub-Saharan Africa has the highest percentage of primary-school-age children out of school
  * Followed by South Asia and Middle East & North Africa
  * The percentage dropped for all these three regions from 2000 to 2019

* From the bar plot

  * More females in primary school age are out of school compare with males in Middle East & North Africa, South Asia and Sub-Saharan Africa

* From the table
  
  * The difference in percentage is the highest in South Asia in 2000, but it dropped significantly in the two decades
  * Same trend can be observed in Middle East & North Africa
  * Percentage of male primary-school-aged children who are out of school is slightly higher than female in Europe & Central Asia and Latin America & Caribbean in 2019


Pupil-student ratio {data-icon="ion-android-search"}
=============================
Row{data-height=150}
--------------------------------------

### **Pupil-teacher ratio**
```{r}
valueBox(value = "Pupil-teacher ratio", icon = "ion-university", caption = "Regional - Income Group - Education level, 2000-2019", color = "coral")
```


Row {.tabset data-height=700}
----------------------------------

### **Pupil-teacher ratio (%) across 2000-2019**
```{r}
df_xq <- data_joined %>%
   pivot_longer(cols = c("2000":"2019"),
               names_to = "year",
               values_to = "value") %>%
 dplyr::filter(str_detect(indicator, 'Pupil-teacher ratio') & !str_detect(value, "NA") & !str_detect(Region, "NA")) %>%
 rename("Income_group" = 'Income group',
        "Indicator" = "indicator") 

df2 <- df_xq %>%
  group_by(Indicator, Region)
  
  time_figure <- ggplot(df2, aes(x = year,
             y = value,
             fill = Indicator)) +
  geom_col(alpha = 0.8, width = 0.85, position = "fill") +
  scale_fill_brewer(palette = "Dark2") +
  scale_y_continuous(expand = c(0, 0.1)) +
  coord_flip() +
  
  labs(
    tittle = "Pupil-teacher ratio (%) between 2000-2019",
    x = "Years",
    y = "Pupil-teacher ratio (%)"
  ) +
  theme_minimal(base_family = "Roboto Condensed") +
  theme(
    plot.margin = margin(0.5, 0.5, 0.5, unit = "cm"),
    plot.title = element_text(size = 15, face = "bold", hjust = 0.5),
    strip.text.y = element_text(angle = 270, face = "bold"),
    strip.placement = "outside",
    axis.title.x = element_text(margin = margin(t = 0.5, b = 0.5, unit = "cm")),
    axis.title.y = element_blank(),
    axis.text = element_text(size = 10),
    panel.grid.major.y = element_blank(),
  ) +
    ggtitle("Pupil-teacher ratio (%) between 2000-2019")

plotly::ggplotly(time_figure)
```  
  
### **Worldwide Highest Average Pupil-teacher ratio**
```{r Table1}
 df1 <- df_xq %>%
  group_by(Indicator, Region) %>%
  select(Indicator, Region, value) %>%
  summarise(Average_ratio = mean(value, na.rm = TRUE)) %>% 
  arrange(-Average_ratio) %>% 
  kable(caption =  "Average Pupil-teacher ratio between 2000-2019 in each Region") %>%
  kable_styling (bootstrap_options = c("striped", "hover")) 
  
  
df1
```  

  
### **Worldwide Lowest Average Pupil-teacher ratio**
```{r Table2}
 df3 <- df_xq %>%
  group_by(Indicator, Region) %>%
  select(Indicator, Region, value) %>%
  summarise(Average_ratio = mean(value, na.rm = TRUE)) %>% 
  arrange(Average_ratio) %>% 
  kable(caption =  "Regional Average Pupil-teacher ratio between 2000-2019") %>%
  kable_styling (bootstrap_options = c("striped", "hover")) 
  
  
df3
```    

### **Average pupil-teacher ratio on each income group across regions**
```{r Figure2, warning=FALSE}

library(ggthemes)
df3 <- df_xq %>%
  select("Indicator", "Income_group", "value", "Region") %>%
  group_by(Income_group, Indicator, Region) %>%
  summarise(Average_ratio = mean(value, na.rm = TRUE)) 

region_ratio <- ggplot(df3, aes(x = Average_ratio,
                      y = Indicator,
                      fill = Income_group,
                      color = Income_group)) +
  geom_col() +
  facet_wrap( ~ Region) +
  theme_economist() +
  scale_fill_manual(values = economist_pal(fill = TRUE)(4)) +
  scale_color_manual(values = economist_pal(fill = TRUE)(4)) +
  theme(text = element_text(size=5),
        plot.title = element_text(hjust = 0.5)) +
  ggtitle("Average pupil-teacher ratio on different income group in each region")
  
plotly::ggplotly(region_ratio)

```
  
  
Column {.sidebar data-width=400}
--------------------------------------
> Key Findings

* From the first graph (Pupil-teacher ratio(%) between 2000-2019)

  * Pupil-teacher ratio seems similar across 2000-2018
  * In 2019, there's a significant decrease in pupil-teacher ratio especially in tertiary level and primary level. Next, there's an increase pupil-teacher ratio in upper secondary level, secondary level and lower secondary level. 

* From the tables (Highest/Lowest Average Pupil-teacher ratio)

  * Holding other variable constant, the highest average ratio is 24.74901 in Sub-saharan Africa's primary level between 2000-2019
  * Holding other variable constant, the lowest average ratio is 7.883399 in North America's lower-secondary level between 2000-2019

* From the second graph (Average pupil-teacher ratio on different income group in each region)
  
  * Interestingly, it seems that Sub-Saharan Africa, South Asia and Middle East & North Africa has greater proportion in low income group comparing to other countries. 
  * Middle East & North Africa and Sub-Saharan Africa has greater average pupil-teacher ratio in primary level. 
  * Europe& Central Asia has similar average pupil-teacher ratio across region.
  * East Asia & Pacific and Latin America & Caribbean has similar average pupil-teacher ratio.